import mysql.connector
from mysql.connector import Error

# 初始数据
CUSTOMERS_DATA = [
    ('张三', '13800138000', 'zhangsan@example.com', '北京市朝阳区'),
    ('李四', '13900139000', 'lisi@example.com', '上海市浦东新区')
]

BOOKS_DATA = [
    ('三体', '刘慈欣', '重庆出版社', 59.8, 100),
    ('活着', '余华', '作家出版社', 45.0, 50)
]

PRODUCTS_DATA = [
    ('三体主题笔记本', '文具', '科幻主题特制笔记本', 29.9, 200),
    ('红楼梦人物书签', '书签', '经典人物系列书签', 15.0, 150)
]

def create_database():
    try:
        # 连接MySQL服务器
        connection = mysql.connector.connect(
            host="localhost",
            user="root",
            password="123456"  # 修改为您的MySQL root用户密码
        )
        
        if connection.is_connected():
            cursor = connection.cursor()
            
            # 创建数据库
            cursor.execute("DROP DATABASE IF EXISTS bookshop")
            cursor.execute("CREATE DATABASE bookshop")
            print("数据库 'bookshop' 创建成功")
            
            # 使用bookshop数据库
            cursor.execute("USE bookshop")
            
            # 创建customers表
            cursor.execute("""
                CREATE TABLE customers (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(100) NOT NULL,
                    phone VARCHAR(20),
                    email VARCHAR(100),
                    address TEXT
                )
            """)
            print("表 'customers' 创建成功")
            
            # 创建books表
            cursor.execute("""
                CREATE TABLE books (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    title VARCHAR(200) NOT NULL,
                    author VARCHAR(100),
                    publisher VARCHAR(100),
                    price DECIMAL(10,2),
                    stock INT
                )
            """)
            print("表 'books' 创建成功")
            
            # 创建products表
            cursor.execute("""
                CREATE TABLE products (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(200) NOT NULL,
                    category VARCHAR(50),
                    description TEXT,
                    price DECIMAL(10,2),
                    stock INT
                )
            """)
            print("表 'products' 创建成功")
            
            # 插入客户数据
            cursor.executemany("""
                INSERT INTO customers (name, phone, email, address)
                VALUES (%s, %s, %s, %s)
            """, CUSTOMERS_DATA)
            
            # 插入图书数据
            cursor.executemany("""
                INSERT INTO books (title, author, publisher, price, stock)
                VALUES (%s, %s, %s, %s, %s)
            """, BOOKS_DATA)
            
            # 插入文创产品数据
            cursor.executemany("""
                INSERT INTO products (name, category, description, price, stock)
                VALUES (%s, %s, %s, %s, %s)
            """, PRODUCTS_DATA)
            
            connection.commit()
            print("初始数据插入成功")

    except Error as e:
        print(f"错误: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("数据库连接已关闭")

if __name__ == "__main__":
    create_database() 